﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using prjKQXS_DLL.Bean;

namespace prjKQXS_DLL.Dal
{
    public class DMCANBO_DAL
    {
        public DataTable loadAllCanBo(string Ma_DM_DONVI, bool isPublic, int Thanh_Ly)
        {
            SqlConnection conn = new SqlConnection(BienTc.connString);
            DataTable dt = new DataTable();
            try
            {
                if (isPublic == false)
                    {
                        Thanh_Ly = 2;
                    }
                string sql = "";
                if (Thanh_Ly < 2)
                {
                if (Ma_DM_DONVI == "TC")
                {

                    sql = "Select * from DMCANBO INNER JOIN DMDonVi on DMCANBO.Ma_DMDonVi=DMDonVi.Ma_DT where DMCanBo.isPublic = @isPublic and DMCanBo.Thanh_Ly = @Thanh_Ly order by DMCanBo.Ma_DT";
                }
                else
                {
                    sql = "Select * from DMCANBO INNER JOIN DMDonVi on DMCANBO.Ma_DMDonVi=DMDonVi.Ma_DT where DMCanBo.isPublic = @isPublic and Ma_DMDonVi = @Ma_DMDonVi and DMCanBo.Thanh_Ly = @Thanh_Ly order by DMCanBo.Ma_DT";
                }
                }
                else
                    {
                        if (Ma_DM_DONVI == "TC")
                        {

                            sql = "Select * from DMCANBO INNER JOIN DMDonVi on DMCANBO.Ma_DMDonVi=DMDonVi.Ma_DT where DMCanBo.isPublic = @isPublic order by DMCanBo.Ma_DT";
                        }
                        else
                        {
                            sql = "Select * from DMCANBO INNER JOIN DMDonVi on DMCANBO.Ma_DMDonVi=DMDonVi.Ma_DT where DMCanBo.isPublic = @isPublic and Ma_DMDonVi = @Ma_DMDonVi order by DMCanBo.Ma_DT";
                        }
                    }
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                if (Ma_DM_DONVI != "TC")
                {
                    da.SelectCommand.Parameters.AddWithValue("@Ma_DMDonVi", Ma_DM_DONVI);
                }
                da.SelectCommand.Parameters.AddWithValue("@isPublic", isPublic);
                if (Thanh_Ly < 2)
                {
                    if (Thanh_Ly == 0)
                    {
                        da.SelectCommand.Parameters.AddWithValue("@Thanh_Ly", false);
                    }
                    else
                    {
                        da.SelectCommand.Parameters.AddWithValue("@Thanh_Ly", true);
                    }
                }
                da.Fill(dt);
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }

            return dt;
        }
        public DataTable loadAllCanBo12(string Ma_DM_DONVI, bool isPublic)
        {
            SqlConnection conn = new SqlConnection(BienTc.connString);
            DataTable dt = new DataTable();
            try
            {
                string sql = "";
                if (Ma_DM_DONVI == "TC")
                {
                    sql = "Select * from DMCANBO INNER JOIN DMDonVi on DMCANBO.Ma_DMDonVi=DMDonVi.Ma_DT where DMCanBo.Ma_DMChucVu = @Chucvu and DMCanBo.isPublic = @isPublic order by DMCanBo.Ma_DT";

                }
                else
                {
                    sql = "Select * from DMCANBO INNER JOIN DMDonVi on DMCANBO.Ma_DMDonVi=DMDonVi.Ma_DT where DMCanBo.Ma_DMChucVu = @Chucvu and DMCanBo.isPublic = @isPublic and Ma_DMDonVi = @Ma_DMDonVi order by DMCanBo.Ma_DT";
                }
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                if (Ma_DM_DONVI != "TC")
                {
                    da.SelectCommand.Parameters.AddWithValue("@Ma_DMDonVi", Ma_DM_DONVI);
                }
                da.SelectCommand.Parameters.AddWithValue("@isPublic", isPublic);
                da.SelectCommand.Parameters.AddWithValue("@Chucvu", "ĐLC1");
                da.Fill(dt);
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }

            return dt;
        }

        public DataTable loadAllCanBoByLaiXuat(string Ma_DM_DONVI, bool isPublic)
        {
            SqlConnection conn = new SqlConnection(BienTc.connString);
            DataTable dt = new DataTable();
            try
            {
                string sql = "";
                if (Ma_DM_DONVI == "TC")
                {
                    sql = "Select * from DMCANBO  INNER JOIN DMDonVi on DMCANBO.Ma_DMDonVi=DMDonVi.Ma_DT  where DMDonVi.isPublic = @isPublic and  DMCanBo.isPublic = @isPublic  order by DMCanBo.Ma_DT";
                }
                else
                {
                    sql = "Select * from DMCANBO  INNER JOIN DMDonVi on DMCANBO.Ma_DMDonVi=DMDonVi.Ma_DT  where DMDonVi.isPublic = @isPublic and  DMCanBo.isPublic = @isPublic and Ma_DMDonVi = @Ma_DMDonVi order by DMCanBo.Ma_DT";
                }
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                if (Ma_DM_DONVI != "TC")
                {
                    da.SelectCommand.Parameters.AddWithValue("@Ma_DMDonVi", Ma_DM_DONVI);
                }
                da.SelectCommand.Parameters.AddWithValue("@isPublic", isPublic);               
                da.Fill(dt);
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }

            return dt;
        }

        public DataTable loadAllCanBoByLaiXuat_Cap2(string Ma_DM_DONVI, bool isPublic)
        {
            SqlConnection conn = new SqlConnection(BienTc.connString);
            DataTable dt = new DataTable();
            try
            {
                string sql = "";
                if (Ma_DM_DONVI == "TC")
                {
                    sql = "Select * from HopDong  INNER JOIN DMDonVi on DMCANBO.Ma_DMDonVi=DMDonVi.Ma_DT  where DMDonVi.isPublic = @isPublic and  DMCanBo.isPublic = @isPublic  order by DMCanBo.Ma_DT";
                }
                else
                {
                    sql = "Select * from HopDong  INNER JOIN DMDonVi on DMCANBO.Ma_DMDonVi=DMDonVi.Ma_DT  where DMDonVi.isPublic = @isPublic and  DMCanBo.isPublic = @isPublic and Ma_DMDonVi = @Ma_DMDonVi order by DMCanBo.Ma_DT";
                }
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                if (Ma_DM_DONVI != "TC")
                {
                    da.SelectCommand.Parameters.AddWithValue("@Ma_DMDonVi", Ma_DM_DONVI);
                }
                da.SelectCommand.Parameters.AddWithValue("@isPublic", isPublic);
                da.Fill(dt);
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }

            return dt;
        }

        public DataTable loadAllCanBo2(string Ma_DM_DONVI, bool isPublic, int Thanh_Ly)
        {
            SqlConnection conn = new SqlConnection(BienTc.connString);
            DataTable dt = new DataTable();
            try
            {
                string sql = "";
                sql = "Select * from DMCANBO INNER JOIN DMDonVi on DMCANBO.Ma_DMDonVi=DMDonVi.Ma_DT where DMDonVi.isPublic = @isPublic and  DMCanBo.isPublic = @isPublic and DMCanBo.Thanh_Ly = @Thanh_Ly and Ma_DMDonVi = @Ma_DMDonVi";
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                da.SelectCommand.Parameters.AddWithValue("@Ma_DMDonVi", Ma_DM_DONVI);
                da.SelectCommand.Parameters.AddWithValue("@isPublic", isPublic);
                da.SelectCommand.Parameters.AddWithValue("@Thanh_Ly", Thanh_Ly);
                da.Fill(dt);
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }

            return dt;
        }

        public int InsertDMCANBOnull(DMCANBO dmcanbo)
        {
            SqlConnection conn = new SqlConnection(BienTc.connString);
            conn.Open();
            SqlCommand sqlCommand = new SqlCommand("insert into [DMCANBO](Ma_DT,Ten_DT,SDT,Ma_DMChucVu,Tuyen,Ma_DMDonVi,Noi_O,MST,CMT,Noi_Cap,isPublic) values(@Ma_DT,@Ten_DT,@SDT,@Ma_DMChucVu,@Tuyen,@Ma_DMDonVi,@Noi_O,@MST,@CMT,@Noi_Cap,@isPublic)", conn);
            try
            {
                sqlCommand.Parameters.AddWithValue("@Ma_DT", dmcanbo.Ma_DT);
                sqlCommand.Parameters.AddWithValue("@Ten_DT", dmcanbo.Ten_DT);
                sqlCommand.Parameters.AddWithValue("@SDT", dmcanbo.SDT);
                sqlCommand.Parameters.AddWithValue("@Ma_DMChucVu", dmcanbo.Ma_DMChucVu);
                sqlCommand.Parameters.AddWithValue("@Tuyen", dmcanbo.Tuyen);
                sqlCommand.Parameters.AddWithValue("@Ma_DMDonVi", dmcanbo.Ma_DMDonVi);
                sqlCommand.Parameters.AddWithValue("@Noi_O", dmcanbo.Noi_O);
                sqlCommand.Parameters.AddWithValue("@MST", dmcanbo.MST);
                sqlCommand.Parameters.AddWithValue("@CMT", dmcanbo.CMT);
                sqlCommand.Parameters.AddWithValue("@Noi_Cap", dmcanbo.Noi_Cap);
                //sqlCommand.Parameters.AddWithValue("@Ngay_Cap", dmcanbo.Ngay_Cap);
                sqlCommand.Parameters.AddWithValue("@isPublic", true);
                return sqlCommand.ExecuteNonQuery();
            }
            catch
            {
                throw;
            }
            finally
            {
                sqlCommand.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }
        public int InsertDMQUYnull(DMQUY dmquy)
        {
            SqlConnection conn = new SqlConnection(BienTc.connString);
            conn.Open();
            SqlCommand sqlCommand = new SqlCommand("insert into [DMQUY](Ngay,Sophieuthu,Sophieuchi,Noidung,Sotienchi,Sotienthu,Tonquy,Ngaychungtu,Madv) values(@Ngay,@Sophieuthu,@Sophieuchi,@Noidung,@Sotienchi,@Sotienthu,@Tonquy,@Ngaychungtu,@Madv)", conn);
            try
            {
                sqlCommand.Parameters.AddWithValue("@Ngay", dmquy.Ngay);
                sqlCommand.Parameters.AddWithValue("@Sophieuthu", dmquy.Sophieuthu);
                sqlCommand.Parameters.AddWithValue("@Sophieuchi", dmquy.Sophieuchi);
                sqlCommand.Parameters.AddWithValue("@Noidung", dmquy.Noidung);
                sqlCommand.Parameters.AddWithValue("@Sotienthu", dmquy.Sotienthu);
                sqlCommand.Parameters.AddWithValue("@Sotienchi", dmquy.Sotienchi);
                sqlCommand.Parameters.AddWithValue("@Tonquy", dmquy.Tonquy);
                sqlCommand.Parameters.AddWithValue("@Ngaychungtu", dmquy.Ngaychungtu);
                sqlCommand.Parameters.AddWithValue("@Madv", dmquy.Madv);
                return sqlCommand.ExecuteNonQuery();
            }
            catch
            {
                throw;
            }
            finally
            {
                sqlCommand.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }
        public bool checkMa_DT(string ma_dt)
        {
            bool retVal = false;
            SqlConnection conn = new SqlConnection(BienTc.connString);
            try
            {
                SqlCommand cmd = new SqlCommand("Select count(*) from DMCANBO where MA_DT = @MA_DT and Thanh_Ly=0", conn);
                cmd.Parameters.AddWithValue("@MA_DT", ma_dt);
                conn.Open();
                if (cmd.ExecuteScalar().ToString() == "0")
                {
                    retVal = true;
                }
                else
                {
                    retVal = false;
                }
            }
            catch (Exception)
            {
                retVal = false;
            }
            finally
            {
            }
            return retVal;
        }
        public bool Ktrasohdcb(string ma_dt)
        {
            bool retVal = false;
            SqlConnection conn = new SqlConnection(BienTc.connString);
            try
            {
                SqlCommand cmd = new SqlCommand("Select count(*) from DMCANBO where Sohd = @MA_DT", conn);
                cmd.Parameters.AddWithValue("@MA_DT", ma_dt);
                conn.Open();
                if (cmd.ExecuteScalar().ToString() == "0")
                {
                    retVal = true;
                }
                else
                {
                    retVal = false;
                }
            }
            catch (Exception)
            {
                retVal = false;
            }
            finally
            {
            }
            return retVal;
        }
        public bool KiemtraMST(string MST)
        {
            bool retVal = false;
            SqlConnection conn = new SqlConnection(BienTc.connString);
            try
            {
                SqlCommand cmd = new SqlCommand("Select count(*) from DMCANBO where MST = @MST ", conn);
                cmd.Parameters.AddWithValue("@MST", MST);
                conn.Open();
                if (cmd.ExecuteScalar().ToString() == "0")
                {
                    retVal = true;
                }
                else
                {
                    retVal = false;
                }
            }
            catch (Exception)
            {
                retVal = false;
            }
            finally
            {
            }
            return retVal;
        }
        public bool KiemtraCMND(string CMT)
        {
            bool retVal = false;
            SqlConnection conn = new SqlConnection(BienTc.connString);
            try
            {
                SqlCommand cmd = new SqlCommand("Select count(*) from DMCANBO where CMT = @CMT", conn);
                cmd.Parameters.AddWithValue("@CMT", CMT);
                conn.Open();
                if (cmd.ExecuteScalar().ToString() == "0")
                {
                    retVal = true;
                }
                else
                {
                    retVal = false;
                }
            }
            catch (Exception)
            {
                retVal = false;
            }
            finally
            {
            }
            return retVal;
        }

        public bool Kiemtrasophieuthu(string soct)
        {
            try
            {
                SqlConnection conn = new SqlConnection(BienTc.connString);
                conn.Open();
                bool reVal;
                SqlDataAdapter da = new SqlDataAdapter("Select * from DMCANBO where So_CT = @So_CT ", conn);
                da.SelectCommand.Parameters.AddWithValue("@So_CT", soct);
                DataTable dt = new DataTable();
                da.Fill(dt);
                if (dt.Rows.Count > 0)
                {
                    reVal = true;
                }
                else
                {
                    reVal = false;
                }
                return reVal;
            }
            catch
            {
                throw;
            }
        }
        //===================================
        public bool checkMa_CV(string ma_dt)
        {
            bool retVal = false;
            SqlConnection conn = new SqlConnection(BienTc.connString);
            try
            {
                SqlCommand cmd = new SqlCommand("Select count(*) from DMCHUCVU where MA_DT = @MA_DT ", conn);
                cmd.Parameters.AddWithValue("@MA_DT", ma_dt);
                conn.Open();
                if (cmd.ExecuteScalar().ToString() == "0")
                {
                    retVal = true;
                }
                else
                {
                    retVal = false;
                }
            }
            catch (Exception)
            {
                retVal = false;
            }
            finally
            {
            }
            return retVal;
        }
      
        //===================================
    }
}
